-- 首先创建名为bull的可登录用户(role)，其密码为password，确保其有创建数据库的权限
-- 在PostgreSQL的bin目录下运行：createuser -d -P -U postgres bull
-- 其次创建名为bull的库，其编码为UTF8，owner为bull
-- 在PostgreSQL的bin目录下运行：createdb -E UTF8 -O bull -U bull bull

drop sequence if exists seq_global_id;
create sequence seq_global_id start with 100;

create or replace function f_set_last_modified() returns trigger as $$
begin
    new.c_last_modified_date := now();
    return new;
end;
$$ language plpgsql;

-- 用户基础信息表，用于登录操作
drop table if exists t_user;
create table t_user (
    c_id serial primary key,
    c_login_name varchar(64) not null unique,
    c_status int not null default 0,    -- 0: valid, 1: invalid, 10: deleted
    c_name varchar(64) not null,        -- Nickname
    c_password varchar(255) not null,
    c_salt varchar(64) not null,
    c_roles varchar(255) not null,
    c_register_date timestamp not null
);

drop table if exists t_user_admin;
create table t_user_admin (
    c_id serial primary key
);

drop table if exists t_user_member;
create table t_user_member (
    c_id serial primary key,
    c_cell varchar(20),
    c_phone varchar(30)
);

drop table if exists t_shop;
create table t_shop (
    c_id serial primary key,
    c_member_id bigint not null,
    c_name varchar(128) not null,
    c_domain_name varchar(128) not null,
    c_status int not null default 0,    -- 0: valid, 1: invalid, 10: deleted
    c_homepage varchar(256),
    c_contactor varchar(20),
    c_cellphone varchar(20),
    c_phone varchar(20),
    c_fax varchar(20),
    c_email varchar(50),
    c_introduction TEXT
);

drop table if exists t_category;
create table t_category (
    c_id serial primary key,
    c_shop_id bigint not null,
    c_name varchar(128) not null,
    c_status int not null default 0,    -- 0: valid, 1: invalid, 10: deleted
    c_order int,
    c_level int,
    c_level1_id bigint,
    c_level2_id bigint,
    c_level3_id bigint
);

drop table if exists t_product cascade;
create table t_product (
    c_id serial primary key,
    c_shop_id bigint references t_shop(c_id),
    c_title varchar(128) not null,
    c_desc varchar(1000),
    c_status int not null default 0,    -- 0: valid, 1: invalid, 10: deleted
    c_order int not null default 0, -- default value set by application should be timestamp
    c_price numeric(10,2)
);

drop table if exists t_link;
create table t_link (
    c_category_id bigint references t_category(c_id),
    c_product_id bigint references t_product(c_id),
    c_create_date date not null,
    primary key (c_category_id, c_product_id)
);

drop table if exists t_product_image;
create table t_product_image (
    c_id serial primary key,
    c_product_id bigint references t_product(c_id),
    c_path varchar(100) not null,
    c_order bigint not null default 0 -- default value set by application should be timestamp
);
